package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.FincDisburse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.OrganizationDepartmentPrincipal;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 支出表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-05-17
 */
public interface FincDisburseMapper extends BaseMapper<FincDisburse> {




    @Select({"<script>",
            "SELECT ",
            "getSchoolName(t1.school_id) AS schoolName,",
            "t3.`name` AS fincTypeName,",
            "t2.name AS handleName,",
            "t1.*",
            "FROM finc_disburse AS t1",
            "LEFT JOIN staff AS t2 ON t1.handle_id = t2.id",
            "LEFT JOIN finc_type AS t3 ON t3.id = t1.finc_type_id",
            "WHERE 1 = 1",
            "AND t1.deleted = 0",

            "<if test='handleName != null and handleName !=&apos;&apos;'>",
            "AND t2.`name` LIKE '%${handleName}%'",
            "</if>",

            "<if test='startDate != null and startDate !=&apos;&apos; and endDate != null and endDate !=&apos;&apos;'>",
            "AND t1.handle_date BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "<if test='yearPart != null and yearPart !=&apos;&apos;'>",
            "AND t1.year_part = ${yearPart}",
            "</if>",

            "<if test='quarterNum != null and quarterNum !=&apos;&apos;'>",
            "AND t1.quarter_num = ${quarterNum}",
            "</if>",
            "<if test='type == 1'>",
            "<if test='schoolOrDepartmentId != null and schoolOrDepartmentId !=&apos;&apos;'>",
            "AND t1.school_id = ${schoolOrDepartmentId}",
            "</if>",
            "</if>",

            "<if test='type == 2'>",
            "<if test='schoolOrDepartmentId != null and schoolOrDepartmentId !=&apos;&apos;'>",
            "AND t1.department_id = ${schoolOrDepartmentId}",
            "</if>",
            "</if>",

            "<if test='fincTypeId != null and fincTypeId !=&apos;&apos;'>",
            "AND t1.finc_type_id = ${fincTypeId}",
            "</if>",

            "<if test='fincTypeSubId != null and fincTypeSubId !=&apos;&apos;'>",
            "AND t1.finc_type_sub_id = ${fincTypeSubId}",
            "</if>",
            "ORDER BY t1.handle_date DESC",
            "</script>"})
    public IPage<FincDisburse> findAll(IPage<FincDisburse> page,
                                                          @Param("yearPart") String yearPart,
                                                          @Param("quarterNum") String quarterNum,
                                                          @Param("handleName") String handleName,
                                                          @Param("fincTypeId") String fincTypeId,
                                                          @Param("fincTypeSubId") String fincTypeSubId,
                                                          @Param("schoolOrDepartmentId") String schoolOrDepartmentId,
                                                          @Param("startDate") String startDate,
                                                          @Param("endDate") String endDate,
                                                          @Param("type") String type);

    @Select({"<script>",
            "WITH school_and_depart AS (",
            "    SELECT",
            "        t1.id AS school_or_department_id,",
            "        t1.name AS school_or_department_name",
            "    FROM",
            "        organization t1",
            "    INNER JOIN school_quarter t2 ON t1.id = t2.school_id AND t2.year_part = 2025 AND t2.quarter_num = 2",
            "    UNION",
            "    SELECT",
            "        od.department_id,",
            "        od.department_name",
            "    FROM",
            "        organization_department od",
            "    WHERE",
            "        school_id = 1600466723094175746",
            ")",
            "SELECT * FROM school_and_depart",
           "</script>"})
    public List<Map<String, Object>> getSchoolAndDepart();

    @Update("update finc_disburse set school_id = #{schoolId},department_id = #{departmentId},finc_type_id = #{fincTypeId},finc_type_sub_id = #{fincTypeSubId},handle_id =#{handleId} ,handle_date =#{handleDate} ,disburse_price =#{disbursePrice},disburse_detail =#{disburseDetail},remark =#{remark},created_id =#{adminId} where id = #{id}")
    boolean updateFincDisburseInfo(@Param("schoolId") Long schoolId,
                                  @Param("departmentId") Long departmentId,
                                  @Param("fincTypeId") Long fincTypeId,
                                  @Param("fincTypeSubId") Long fincTypeSubId,
                                  @Param("handleId") Long handleId,
                                  @Param("handleDate") String handleDate,
                                  @Param("disbursePrice") BigDecimal disbursePrice,
                                  @Param("disburseDetail") String disburseDetail,
                                  @Param("remark") String remark,
                                  @Param("adminId") Long adminId,
                                  @Param("id") Long id);



    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "t2.name AS fincTypeName,",
            "(CASE WHEN t1.department_id IS NULL THEN t1.school_id ELSE t1.department_id END) AS school_or_department_id,",
            "SUM(t1.disburse_price) AS total_disburse_price",
            "FROM finc_disburse t1",
            "LEFT JOIN finc_type t2 ON t1.finc_type_id = t2.id",
            "WHERE t1.deleted = 0",

            "<if test='yearPart != null and yearPart !=&apos;&apos;'>",
            "AND t1.year_part = #{yearPart}",
            "</if>",

            "<if test='quarterNum != null and quarterNum !=&apos;&apos;'>",
            "AND t1.quarter_num = #{quarterNum}",
            "</if>",

            "<if test='fincTypeId != null and fincTypeId !=&apos;&apos;'>",
            "AND t1.finc_type_id = #{fincTypeId}",
            "</if>",

            "<if test='fincTypeSubId != null and fincTypeSubId !=&apos;&apos;'>",
            "AND t1.finc_type_sub_id = #{fincTypeSubId}",
            "</if>",

            "<if test='startDate != null and startDate !=&apos;&apos; and endDate != null and endDate !=&apos;&apos;'>",
            "AND t1.handle_date BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "GROUP BY " ,

            "t1.finc_type_id,",
                                "t1.finc_type_sub_id, " ,
                    "school_or_department_id",
//            "ORDER BY t1.handle_date DESC",
            "</script>"})
    public IPage<FincDisburse> fincDisburseCount(IPage<FincDisburse> page,
                                       @Param("yearPart") String yearPart,
                                       @Param("quarterNum") String quarterNum,
                                       @Param("fincTypeId") String fincTypeId,
                                       @Param("fincTypeSubId") String fincTypeSubId,
                                       @Param("startDate") String startDate,
                                       @Param("endDate") String endDate);


    @Select({"<script>",
            "SELECT",
            "t1.finc_type_id,",
            "t1.finc_type_sub_id,",
            "t2.name AS fincTypeName,",
            "t3.name AS finc_type_sub_name",
            "FROM finc_disburse t1",
            "LEFT JOIN finc_type t2 ON t1.finc_type_id = t2.id",
            "LEFT JOIN finc_type t3 ON t1.finc_type_sub_id = t3.id",
            "WHERE t1.deleted = 0",

            "<if test='yearPart != null and yearPart !=&apos;&apos;'>",
            "AND t1.year_part = #{yearPart}",
            "</if>",

            "<if test='quarterNum != null and quarterNum !=&apos;&apos;'>",
            "AND t1.quarter_num = #{quarterNum}",
            "</if>",

            "<if test='fincTypeId != null and fincTypeId !=&apos;&apos;'>",
            "AND t1.finc_type_id = #{fincTypeId}",
            "</if>",

            "<if test='fincTypeSubId != null and fincTypeSubId !=&apos;&apos;'>",
            "AND t1.finc_type_sub_id = #{fincTypeSubId}",
            "</if>",

            "<if test='startDate != null and startDate !=&apos;&apos; and endDate != null and endDate !=&apos;&apos;'>",
            "AND t1.handle_date BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "GROUP BY t1.finc_type_id, t1.finc_type_sub_id",
            "ORDER BY  t1.finc_type_id",
            "</script>"})
    public List<FincDisburse> getFincDisburse(
                                                 @Param("yearPart") String yearPart,
                                                 @Param("quarterNum") String quarterNum,
                                                 @Param("fincTypeId") String fincTypeId,
                                                 @Param("fincTypeSubId") String fincTypeSubId,
                                                 @Param("startDate") String startDate,
                                                 @Param("endDate") String endDate);

    @Select({"<script>",
            "SELECT",
            "t.finc_type_id,",
            "t.finc_type_sub_id,",
            "t.department_id,",
            "getSchoolName(t.school_id) AS schoolOrDepartmentName,",
            "(CASE WHEN t.department_id IS NULL THEN t.school_id ELSE t.department_id END) AS school_or_department_id,",
            "SUM(t.disburse_price) AS total_disburse_price",
            "FROM finc_disburse t",
            "WHERE t.deleted = 0",

            "<if test='yearPart != null and yearPart !=&apos;&apos;'>",
            "AND t.year_part = #{yearPart}",
            "</if>",

            "<if test='quarterNum != null and quarterNum !=&apos;&apos;'>",
            "AND t.quarter_num = #{quarterNum}",
            "</if>",

            "<if test='fincTypeId != null and fincTypeId !=&apos;&apos;'>",
            "AND t.finc_type_id = #{fincTypeId}",
            "</if>",

            "<if test='fincTypeSubId != null and fincTypeSubId !=&apos;&apos;'>",
            "AND t.finc_type_sub_id = #{fincTypeSubId}",
            "</if>",

            "<if test='startDate != null and startDate !=&apos;&apos; and endDate != null and endDate !=&apos;&apos;'>",
            "AND t.handle_date BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "GROUP BY t.finc_type_id, t.finc_type_sub_id, school_or_department_id",
            "ORDER BY t.finc_type_id",
            "</script>"})
    public List<FincDisburse> getFincDisburseFindAll(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("fincTypeId") String fincTypeId, @Param("fincTypeSubId") String fincTypeSubId, @Param("startDate") String startDate, @Param("endDate") String endDate);
}
